
# library(pacman)
# p_load(dplyr, stringr, ggplot2, tidyr, DBI, RSQLite) 
library(dplyr)
library(ggplot2)
library(stringr)
library(tidyr)
library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), "iso_standards.sqlite") # Open database

standards <- dbReadTable(con, "standards_status") # Extract dataset with standards
sectors <- readRDS("sectors.rds") # Open separate dataset with sectors made from the "TC-membership" dataset

dbDisconnect(con) # Disconnect from database

cbPalette <- c("#164688", "#f53f00", "#fad200", "#5f9d03", "#77001f", "#8ecbff",
               "#324000", "#b0cf00", "#492071")

sectormerge <- sectors %>% # Make a merging file with sectors belonging to each committee
  select(committee, sector) %>% 
  unique() 

fig <- standards %>%
  left_join(sectormerge, by = "committee") # Merge with standards dataset to get the variable "sector"

na_sectors <- fig %>% # Some sectors were given NA because they have been coded differently in the TC-membership dataset
  distinct() %>% 
  filter(is.na(sector)) # Identify the NA sectors

na_sectors_filled <- na_sectors %>% # Fill NA sectors with their main committee
  select(-sector) %>%
  mutate(committee2 = str_remove(committee, "\\/SC.*"), # Remove the subcommittee label, which was coded differently
         committee2 = str_squish(committee2)) %>%
  left_join(sectormerge, by = c("committee2" = "committee")) %>% # Add with the main committee. Sub committees belong to the same sectors as theri main committee.
  select(-committee2)

fig <- fig %>%
  anti_join(na_sectors, by = join_by(committee, title)) %>% # Remove the NA sectors
  bind_rows(na_sectors_filled) # Fill in with the cleaned sector dataset with main committees

fig %>%
  mutate(sector = case_when( # Recoding the sectors to what Ruwet (2011) uses
    sector == "Chemicals" ~ "Generalities, infrastructures,\nand sciences",
    sector == "Energy" ~ "Engineering technologies",
    sector == "Food and agriculture" ~ "Agriculture and food technology",
    sector == "Freight" ~ "Transport and distribution of goods",
    sector == "Health" ~ "Health, safety\nand environment",
    sector == "Horizontal subjects" ~ "Generalities, infrastructures,\nand sciences",
    sector == "Information technology" ~ "Electronics, information technology\nand telecommunications",
    sector == "Mechanical engineering" ~ "Engineering technologies",
    sector == "Non-metallic materials" ~ "Materials technologies",
    sector == "Ores and metals" ~ "Materials technologies",
    sector == "Other" ~ "Other",
    sector == "Security" ~ "Health, safety\nand environment",
    sector == "Services" ~ "Generalities, infrastructures,\nand sciences",
    sector == "Special technologies" ~ "Special technologies",
    sector == "Sustainability and environment" ~ "Health, safety\nand environment",
    sector == "Transport" ~ "Transport and distribution of goods",
    sector == "Building and construction" ~ "Construction",
    sector == "Business management and innovation" ~ "Generalities, infrastructures,\nand sciences",
    .default = sector)) %>%
  group_by(year, sector) %>%
  count() %>% # Count number of standards within each sector for each year
  filter(sector != "Other") %>% # Excluding redundancy category
  ungroup() %>%
  replace_na(list(count = 0)) %>% # Replace NA with zeros, since NA indicates that no standards were made within that sector that year
  add_row(year = "2023", sector = "Sustainability and environment", n = 0) %>% # For the recent years, data from ISO is incomplete. 
  add_row(year = "2023", sector = "Services", n = 0) %>% # Filling with 0.
  add_row(year = "2023", sector = "Security", n = 0) %>%
  add_row(year = "2023", sector = "Freight", n = 0) %>%
  add_row(year = "2023", sector = "Business management and innovation", n = 0) %>%
  group_by(sector) %>%
  mutate(aggcount = cumsum(n)) %>% # Cumulative count of standards
  mutate(year = as.numeric(year)) %>% # Making year numeric to fit with the axis of the plot
  mutate(sector = factor(sector, levels = c("Engineering technologies", "Materials technologies", # Same levels as Ruwet (2011)
                                            "Electronics, information technology\nand telecommunications",
                                            "Transport and distribution of goods",
                                            "Generalities, infrastructures,\nand sciences",
                                            "Agriculture and food technology",
                                            "Health, safety\nand environment",
                                            "Construction", "Special technologies"))) %>%
  rename(Sector = sector) %>%
  ggplot(aes(x = year, y = aggcount, fill = Sector, group = Sector)) + # Plotting
  geom_area(position = "stack") +
  scale_fill_manual(values=c(cbPalette), name = "") +
  labs(x = "", y = "")  +
  guides(fill=guide_legend(ncol=3)) +
  theme_classic() +
  theme(legend.text=element_text(size=rel(1.8)),
        legend.position = "bottom",
        legend.title = element_text(size = rel(2.2)),
        axis.text.x = element_text(size = rel(2.2), angle = 90, vjust = 0.5, hjust=1),
        axis.text.y = element_text(size = rel(2.2)))

